/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database;
import data.UserData;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
/**
 *
 * @author Muhammad Dony
 */
public class UserDB extends ConnectDB {
    private String sql;
    private Connection con;
    private Statement stmt;
    private ResultSet rs;
    
    public List getdata() throws SQLException, ClassNotFoundException{
        sql = "SELECT * from `user`";
        return getuserdata(sql);
    }
    
    public List getdataById(String ID) throws SQLException, ClassNotFoundException {
        sql = "SELECT * from `user` WHERE `ID`='"+ID+"';";
        return getuserdata(sql);
    }
        
     private List getuserdata(String sql) throws SQLException, ClassNotFoundException{
        // mempersiapkan list dataPelaporan
        List listdata = new ArrayList();
        //membuat koneksi , createConnection terdapat dalam DBClass
        con = createConnection();
        stmt = con.createStatement();
        rs = stmt.executeQuery(sql);
        // mempersiapkan object dataPelaporan
        UserData listUser;
        
        while(rs.next()){
            listUser = new UserData();
            listUser.setID(rs.getString(1));
            listUser.setFirstname(rs.getString(2));
            listUser.setLastname(rs.getString(3));
            listUser.setEmail(rs.getString(4));
            listUser.setPassword(rs.getString(5)); 
            listdata.add(listUser);
        }
        // menutup semua koneksi db
        rs.close();
        stmt.close();
        con.close();
        //return value list data pelaporan
        return listdata;
    }
    
    private void actdata(String sql) throws ClassNotFoundException, SQLException{
        // membuat koneksi
        con = createConnection();
        stmt = con.createStatement();
        // execute query
        stmt.execute(sql);
        // menutup koneksi db
        stmt.close();
        con.close();
    }
    public void insertData(UserData listUser) throws ClassNotFoundException, SQLException{
        // query
        sql = "INSERT INTO `scholarship`.`user` (`ID`, `FirstName`, `LastName`, `Email`, `Password`) VALUES ('"+listUser.getID()+"', '"+listUser.getFirstname()+"', '"+listUser.getLastname()+"', '"+listUser.getEmail()+"', '"+listUser.getPassword()+"');";
        // memanggil method untuk execute query
        actdata(sql);
        sql="INSERT INTO `scholarship`.`identitas` (`ID`, `nama1`, `nama2`, `tempat`, `tanggal`, `bulan`, `tahun`, `alamat`, `hp`, `email`, `jurusan`, `fakultas`, `universitas`, `tinggi`, `berat`, `darah`, `motto`) VALUES ('"+listUser.getID()+"', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '');";
        actdata(sql);

    }
    public void updateData(UserData listUser) throws ClassNotFoundException, SQLException{
        // query
        sql = "UPDATE `user` SET `FirstName`='"+listUser.getFirstname()+
                "',`LastName`='"+listUser.getLastname()+
                "',`Email`='"+listUser.getEmail()+
                "',`Password`='"+listUser.getPassword()
                +"' WHERE `ID`='"+listUser.getID()+"';";
        // memanggil method untuk execute query
        actdata(sql);
    }
    public void deleteData(String ID) throws ClassNotFoundException, SQLException{
        sql = "DELETE FROM `user` WHERE `ID`='"+ID+"';";
        actdata(sql);
        sql = "DELETE FROM `identitas` WHERE `ID`='"+ID+"';";
        actdata(sql);
    }
}
